package com.CommunityHub.kiana.dao;


import com.CommunityHub.kiana.po.User;
import com.CommunityHub.kiana.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    public User login(String username, String password) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ? AND password = ? AND status = 1";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        User user = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRole(rs.getString("role"));
                user.setSex(rs.getString("sex"));
                user.setBirthData(rs.getDate("birth_data"));
                user.setPhone(rs.getString("phone"));
                user.setIdCard(rs.getString("id_card"));
                user.setContactName(rs.getString("contact_name"));
                user.setContactPhone(rs.getString("contact_phone"));
                user.setMemberType(rs.getString("member_type"));
                user.setStatus(rs.getInt("status"));
            }
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }

        return user;
    }

    public List<User> getAllUsers() throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM users";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRole(rs.getString("role"));
                user.setSex(rs.getString("sex"));
                user.setBirthData(rs.getDate("birth_data"));
                user.setPhone(rs.getString("phone"));
                user.setContactName(rs.getString("contact_name"));
                user.setContactPhone(rs.getString("contact_phone"));
                user.setMemberType(rs.getString("member_type"));
                user.setStatus(rs.getInt("status"));
                users.add(user);
            }
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }

        return users;
    }

    public boolean updateUser(User user) throws SQLException {
        String sql = "UPDATE users SET " +
                "username=?, password=?, role=?, sex=?, birth_data=?, phone=?, id_card=?, " +
                "contact_name=?, contact_phone=?, member_type=?, status=? " +
                "WHERE id=?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getRole());
            pstmt.setString(4, user.getSex());
            pstmt.setDate(5, new Date(user.getBirthData().getTime()));
            pstmt.setString(6, user.getPhone());
            pstmt.setString(7, user.getIdCard());
            pstmt.setString(8, user.getContactName());
            pstmt.setString(9, user.getContactPhone());
            pstmt.setString(10, user.getMemberType());
            pstmt.setInt(11, user.getStatus());
            pstmt.setInt(12, user.getId());

            return pstmt.executeUpdate() > 0;
        }
    }
    public boolean addUser(User user) throws SQLException {
        String sql = "INSERT INTO users (username, password, role, sex, birth_data, phone, id_card, " +
                "contact_name, contact_phone, member_type, status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getRole());
            pstmt.setString(4, user.getSex());
            pstmt.setDate(5, new java.sql.Date(user.getBirthData().getTime()));
            pstmt.setString(6, user.getPhone());
            pstmt.setString(7, user.getIdCard());
            pstmt.setString(8, user.getContactName());
            pstmt.setString(9, user.getContactPhone());
            pstmt.setString(10, user.getMemberType());
            pstmt.setInt(11, user.getStatus());

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }
    public boolean deleteUser(int userId) throws SQLException {
        String sql = "DELETE FROM users WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }
    public User findByUsername(String username) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();

            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRole(rs.getString("role"));
                user.setSex(rs.getString("sex"));
                user.setBirthData(rs.getDate("birth_data"));
                user.setPhone(rs.getString("phone"));
                user.setIdCard(rs.getString("id_card"));
                user.setContactName(rs.getString("contact_name"));
                user.setContactPhone(rs.getString("contact_phone"));
                user.setMemberType(rs.getString("member_type"));
                // user.setServiceObjects(rs.getString("service_objects"));
                //user.setPermissions(rs.getString("permissions"));
                user.setStatus(rs.getInt("status"));
                return user;
            }
            return null;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    public int getAllCountUsers() throws SQLException {
        String sql = "SELECT COUNT(*) FROM users";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int count = 0;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if (rs.next()) {
                return rs.getInt(1); // 获取第一列的计数结果
            }

        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }

        return 0;
    }
}
